﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using AMPSDAO.Entity.Attributes;
using AMPSDAO.DAO.DatabaseManagement;
using AMPSDAO.DAO;
using AMPSDAO;

namespace AMPSDAO.Entity.Managment
{
    public class MappingDatabaseManagement
    {
        private static string columnSyntax = "`{name}` {datatype}{length} {notnull},";
        private static string primaryKeySyntax = "PRIMARY KEY ({columnname})";
        private static string tableSyntax = "CREATE TABLE `{tablename}` ("
                                     +" {columndata}"
                                     +" {primarykey}"
                                     + ") ENGINE=InnoDB DEFAULT CHARSET=utf8";
        private static string foreinKeySyntaxKeyName = "FK_{0}_{1}";
        private static string foreinKeySyntax = "ALTER TABLE `{tablename}` "+
                                                "ADD CONSTRAINT `{keyname}` "+
                                                "FOREIGN KEY (`{columnname}`) REFERENCES `{foreintablename}`(`{foreincolumnname}`)";

        public static MappingSQL buildCreateTableQuery(String tablename, PropertyInfo[] columns)
        {
            MappingSQL mappingSQL = new MappingSQL();
            string sql = tableSyntax;
            string primary = primaryKeySyntax;
            string forein = "";
            string column = "";

            foreach (PropertyInfo prop in columns)
            {
                object[] attrs = prop.GetCustomAttributes(true);
                foreach (object objectAttr in attrs)
                {
                    if (typeof(ColumnAttribute) == objectAttr.GetType())
                    {
                        ColumnAttribute attr = (ColumnAttribute)objectAttr;
                        string name = attr.Name;
                        if (name == "" || name == null)
                        {
                            name = prop.Name;
                        }
                        bool notNull = attr.NotNull;
                        bool primaryKey = attr.PrimaryKey;
                        int length = attr.Length;
                        string dataType = attr.DataType;
                        bool foreinKey = attr.ForeinKey;
                        if (dataType == "" || dataType == null)
                        {
                            dataType = getDataType(prop.PropertyType);
                        }
                        if (primaryKey)
                        {
                            primary = primary.Replace("{columnname}", "`"+name+"`" + ", {columnname}");
                        }
                        if (foreinKey)
                        {
                            string foreinKeyName = String.Format(foreinKeySyntaxKeyName, tablename, name);
                            string foreinTableName = prop.PropertyType.Name;
                            PropertyInfo foreinColumnName = getPrimaryKey(prop);
                            dataType = getDataType(foreinColumnName.PropertyType);
                            forein = foreinKeySyntax;
                            forein = forein.Replace("{tablename}", tablename);
                            forein = forein.Replace("{keyname}", foreinKeyName);
                            forein = forein.Replace("{columnname}", name);
                            forein = forein.Replace("{foreintablename}", prop.PropertyType.Name);
                            forein = forein.Replace("{foreincolumnname}", foreinColumnName.Name);
                            mappingSQL.CreateForeinKeySQL.Add(forein);
                        }
                       
                        column += columnSyntax;
                        column = column.Replace("{name}", name);
                        column = column.Replace("{datatype}", dataType);
                        column = column.Replace("{length}", ((length > 0)?"("+length.ToString()+")":""));
                        column = column.Replace("{notnull}", ((notNull) ? "NOT NULL" : ""));
                    }
                }              
            }

            if (column == "")
            {
                mappingSQL.CreateTasbleSQL = "";
            }
            else
            {
                if (primary.Length > 0 && primary.Contains(", {columnname}"))
                {
                    primary = primary.Replace(", {columnname}", "");
                }

                sql = sql.Replace("{tablename}", tablename);
                sql = sql.Replace("{columndata}", column);
                sql = sql.Replace("{primarykey}", primary);
                mappingSQL.CreateTasbleSQL = sql;
            }

            return mappingSQL;
        }

        private static string getDataType(Type type)
        {
            if (type == typeof(long))
            {
                return "bigint";
            }
            else if (type == typeof(int))
            {
                return "int";
            }
            else if(type == typeof(DateTime))
            {
                return "datetime";
            }
            else if(type == typeof(string)) {
                return "nvarchar";
            }
            else if (type == typeof(bool))
            {
                return "tinyint";
            }
            else
            {
                return "text";
            }
        }

        public static PropertyInfo getPrimaryKey(PropertyInfo entity)
        {
            PropertyInfo[] props = entity.PropertyType.GetProperties();
            foreach (PropertyInfo item in props)
            {
                object[] attrs = item.GetCustomAttributes(true);
                foreach (object objectAtrr in attrs)
                {
                    if (typeof(ColumnAttribute) == objectAtrr.GetType())
                    {
                        ColumnAttribute attr = (ColumnAttribute)objectAtrr;
                        if (attr.PrimaryKey == true)
                        {
                            return item;
                        }
                    }
                }
            }
            return null;
        }

        public static void clearDatabase()
        {
            DatabaseProcesser db = new DatabaseProcesser(DatabaseType.MySQL);
            db.Open();
            try
            {
                Type[] typelist = GetTypesInNamespace(Assembly.GetExecutingAssembly(), "AMPSDAO.Entity.Entitys");
                List<string> listForeinKey = new List<string>();
                typelist = SortTypeList(typelist);

                for (int i = typelist.Length - 1; i > -1; i--)
                {
                    string dropsql = "DROP TABLE IF EXISTS `{tablename}`";
                    dropsql = dropsql.Replace("{tablename}", typelist[i].Name);
                    db.ExecuteCreateCommand(dropsql);
                }

                db.Commit();
                db.CloseConnection();
            }
            catch (Exception e)
            {
                db.RollBack();
                db.CloseConnection();
            }
        }

        public static void CreateDatabaseFormEntity()
        {
            DatabaseProcesser db = new DatabaseProcesser(DatabaseType.MySQL);
            db.Open();
            //db.Transaction = db.Connection.BeginTransaction();
            try
            {
                Type[] typelist = GetTypesInNamespace(Assembly.GetExecutingAssembly(), "AMPSDAO.Entity.Entitys");
                List<string> listForeinKey = new List<string>();
                typelist = SortTypeList(typelist);

                for (int i = typelist.Length -1; i > -1; i--)
                {
                    string dropsql = "DROP TABLE IF EXISTS `{tablename}`";
                    dropsql = dropsql.Replace("{tablename}", typelist[i].Name);
                    db.ExecuteCreateCommand(dropsql);
                }

                for (int i = 0; i < typelist.Length; i++)
                {
                    PropertyInfo[] props = typelist[i].GetProperties();
                    string tableName = typelist[i].Name;
                    List<ColumnAttribute> columnAttr = new List<ColumnAttribute>();
                    MappingSQL sql = buildCreateTableQuery(tableName, props);
                    if (sql.CreateTasbleSQL != "")
                    {
                        db.ExecuteCreateCommand(sql.CreateTasbleSQL);
                    }
                    if (sql.CreateForeinKeySQL.Count > 0)
                    {
                        listForeinKey.AddRange(sql.CreateForeinKeySQL);
                    }
                }

                for (int i = 0; i < listForeinKey.Count; i++)
                {
                    db.ExecuteCreateCommand(listForeinKey[i]);
                }
                db.Commit();
                db.CloseConnection();
            }
            catch(Exception e)
            {
                db.RollBack();
                db.CloseConnection();
            }
        }

        private static Type[] SortTypeList(Type[] typeList)
        {
            List<Type> unForeinTable = new List<Type>();
            List<Type> foreinTable = new List<Type>();

            foreach (Type item in typeList)
            {
                if (IsForeinTable(item))
                {
                    foreinTable.Add(item);
                }
                else
                {
                    unForeinTable.Add(item);
                }
            }
            unForeinTable.AddRange(foreinTable);
            typeList = unForeinTable.ToArray();
            return typeList;
        }

        private static bool IsForeinTable(Type type) 
        { 
            PropertyInfo[] props = type.GetProperties();
            foreach (PropertyInfo prop in props)
            {
                object[] attrs = prop.GetCustomAttributes(true);
                foreach (object objectAttr in attrs)
                {
                    if (typeof(ColumnAttribute) == objectAttr.GetType())
                    {
                        ColumnAttribute attr = (ColumnAttribute)objectAttr;
                        if (attr.ForeinKey == true)
                        {
                            return true;
                        }
                    }
                }
            }

            return false;
        }
            
        private static Type[] GetTypesInNamespace(Assembly assembly, string nameSpace)
        {
            return assembly.GetTypes().Where(t => String.Equals(t.Namespace, nameSpace, StringComparison.Ordinal)).ToArray();
        }
    }
}
